/******* YCHOI item master new UOM addition 07/27/2012 ***/
-- clear table

USE [RD_COMMON]
GO


truncate Table dbo.Star_Item
go






/**** drop article ****/
use RD_DWCR
go

	if exists(select * from distribution.dbo.MSarticles where article = 'JDEINItemMaster')	
exec sp_droparticle @publication = N'RD_DWCR', @article = N'JDEINItemMaster', @force_invalidate_snapshot = 1



-- Drop Indexes

USE [RD_COMMON]
GO

/****** Object:  Index [IX_Item_ItemNumberID]    Script Date: 07/27/2012 13:06:58 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Star_Item]') AND name = N'IX_Item_ItemNumberID')
DROP INDEX [IX_Item_ItemNumberID] ON [dbo].[Star_Item] WITH ( ONLINE = OFF )
GO

/****** Object:  Index [missing_index_119]    Script Date: 07/27/2012 13:06:58 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Star_Item]') AND name = N'missing_index_119')
DROP INDEX [missing_index_119] ON [dbo].[Star_Item] WITH ( ONLINE = OFF )
GO

/****** Object:  Index [missing_index_172]    Script Date: 07/27/2012 13:06:58 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Star_Item]') AND name = N'missing_index_172')
DROP INDEX [missing_index_172] ON [dbo].[Star_Item] WITH ( ONLINE = OFF )
GO

/****** Object:  Index [missing_index_433]    Script Date: 07/27/2012 13:06:58 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Star_Item]') AND name = N'missing_index_433')
DROP INDEX [missing_index_433] ON [dbo].[Star_Item] WITH ( ONLINE = OFF )
GO

/****** Object:  Index [PK_Star_Item]    Script Date: 07/27/2012 13:06:58 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Star_Item]') AND name = N'PK_Star_Item')
ALTER TABLE [dbo].[Star_Item] DROP CONSTRAINT [PK_Star_Item]
GO




/****** Drop Table  Object:  Table [dbo].[Star_Item]    Script Date: 07/27/2012 13:02:46 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Star_Item]') AND type in (N'U'))
DROP TABLE [dbo].[Star_Item]
GO



/******** create article *******/
use RD_DWCR
go

exec sp_addarticle @publication = N'RD_DWCR', @article = N'JDEINItemMaster', @source_owner = N'dbo', @source_object = N'JDEINItemMaster', @destination_table = N'JDEINItemMaster', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'none', @schema_option = 0x0000000002005081, @status = 0, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_JDEINItemMaster', @del_cmd = N'XCALL sp_MSdel_JDEINItemMaster', @upd_cmd = N'XCALL sp_MSupd_JDEINItemMaster', @filter = null, @sync_object = null, @auto_identity_range = N'false'



USE [RD_COMMON]
GO

/****** Object:  Table [dbo].[Star_Item]    Script Date: 07/27/2012 12:58:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[Star_Item](
	[ItemNumberID] [bigint] NOT NULL,
	[SourceID] [int] NOT NULL,
	[Source] [char](20) NOT NULL,
	[ItemNumber] [int] NOT NULL,
	[ItemNumber02] [char](25) NOT NULL,
	[ItemNumber03] [char](25) NOT NULL,
	[Description01] [char](30) NOT NULL,
	[Description02] [char](30) NOT NULL,
	[SalesCategory01Code] [char](6) NOT NULL,
	[SalesCategory01] [varchar](255) NOT NULL,
	[SalesCategory02Code] [char](6) NOT NULL,
	[SalesCategory02] [varchar](255) NOT NULL,
	[SalesCategory03Code] [char](6) NOT NULL,
	[SalesCategory03] [varchar](255) NOT NULL,
	[SalesCategory04Code] [char](6) NOT NULL,
	[SalesCategory04] [varchar](255) NOT NULL,
	[SalesCategory05Code] [char](6) NOT NULL,
	[SalesCategory05] [varchar](255) NOT NULL,
	[SalesCategory06Code] [char](6) NOT NULL,
	[SalesCategory06] [varchar](255) NOT NULL,
	[SalesCategory07Code] [char](6) NOT NULL,
	[SalesCategory07] [varchar](255) NOT NULL,
	[SalesCategory08Code] [char](6) NOT NULL,
	[SalesCategory08] [varchar](255) NOT NULL,
	[SalesCategory09Code] [char](6) NOT NULL,
	[SalesCategory09] [varchar](255) NOT NULL,
	[SalesCategory10Code] [char](6) NOT NULL,
	[SalesCategory10] [varchar](255) NOT NULL,
	[PurchasingCategory01Code] [char](6) NOT NULL,
	[PurchasingCategory01] [varchar](255) NOT NULL,
	[PurchasingCategory02Code] [char](6) NOT NULL,
	[PurchasingCategory02] [varchar](255) NOT NULL,
	[PurchasingCategory03Code] [char](6) NOT NULL,
	[PurchasingCategory03] [varchar](255) NOT NULL,
	[PurchasingCategory04Code] [char](6) NOT NULL,
	[PurchasingCategory04] [varchar](255) NOT NULL,
	[PurchasingCategory05Code] [char](6) NOT NULL,
	[PurchasingCategory05] [varchar](255) NOT NULL,
	[PurchasingCategory06Code] [char](6) NOT NULL,
	[PurchasingCategory06] [varchar](255) NOT NULL,
	[PurchasingCategory07Code] [char](6) NOT NULL,
	[PurchasingCategory07] [varchar](255) NOT NULL,
	[PurchasingCategory08Code] [char](6) NOT NULL,
	[PurchasingCategory08] [varchar](255) NOT NULL,
	[PurchasingCategory09Code] [char](6) NOT NULL,
	[PurchasingCategory09] [varchar](255) NOT NULL,
	[PurchasingCategory10Code] [char](6) NOT NULL,
	[PurchasingCategory10] [varchar](255) NOT NULL,
	--ychoi 07/27/2012 begin
	[UnitOfMeasurePrimary] [char](2) NOT NULL,
	[UnitOfMeasureSecondary] [char](2) NOT NULL,
	[UnitOfMeasurePurchas] [char](2) NOT NULL,
	[UnitOfMeasurePricing] [char](2) NOT NULL,
	[UnitOfMeasureShipping] [char](2) NOT NULL,
	[UnitOfMeasureProduction] [char](2) NOT NULL,
	[UnitOfMeasureAllocation] [char](2) NOT NULL,
	[UnitOfMeasureWeight] [char](2) NOT NULL,
	[UOMVolume] [char](2) NOT NULL,
	[UOMStocking] [char](2) NOT NULL,
	[UOMVolumeOrWeightPSAU] [char](1) NOT NULL,
	[CycleCountCategory] [char](3) NOT NULL,
	[GLCategory] [char](4) NOT NULL,
	-- EDN ychoi
	[ProductGroupCode] [char](3) NOT NULL,
	[ProductGroup] [char](30) NOT NULL,
	[ItemPriceGroupCode] [char](8) NOT NULL,
	[ItemPriceGroup] [char](30) NOT NULL,
	[StockingTypeCode] [char](1) NOT NULL,
	[StockingType] [char](30) NOT NULL,
	[InsertIndicator] [int] NOT NULL,
	[InsertDate] [datetime] NOT NULL,
	[ChangeIndicator] [int] NOT NULL,
	[ChangeDate] [datetime] NOT NULL,
	[DeleteIndicator] [int] NOT NULL,
	[DeleteDate] [datetime] NOT NULL,
	[PurgeDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Star_Item] PRIMARY KEY CLUSTERED 
(
	[ItemNumber] ASC,
	[SourceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/**** DI Steps **/
-- create taget table in DI
-- create mapping in DI
-- RUN refresh
-- 
/*** end of DI ***/


-- create indexes 

USE [RD_COMMON]
GO

/****** Object:  Index [IX_Item_ItemNumberID]    Script Date: 07/27/2012 13:08:01 ******/
CREATE NONCLUSTERED INDEX [IX_Item_ItemNumberID] ON [dbo].[Star_Item] 
(
	[ItemNumberID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [missing_index_119]    Script Date: 07/27/2012 13:08:01 ******/
CREATE NONCLUSTERED INDEX [missing_index_119] ON [dbo].[Star_Item] 
(
	[SalesCategory01Code] ASC
)
INCLUDE ( [ItemNumberID],
[SourceID],
[Source],
[ItemNumber],
[ItemNumber02],
[ItemNumber03],
[Description01],
[Description02],
[SalesCategory01],
[SalesCategory02Code],
[SalesCategory02],
[SalesCategory03Code],
[SalesCategory03],
[SalesCategory04Code],
[SalesCategory04],
[SalesCategory05Code],
[SalesCategory05],
[SalesCategory06Code],
[SalesCategory06],
[SalesCategory07Code],
[SalesCategory07],
[SalesCategory08Code],
[SalesCategory08],
[SalesCategory09Code],
[SalesCategory09],
[SalesCategory10Code],
[SalesCategory10],
[PurchasingCategory01Code],
[PurchasingCategory01],
[PurchasingCategory02Code],
[PurchasingCategory02],
[PurchasingCategory03Code],
[PurchasingCategory03],
[PurchasingCategory04Code],
[PurchasingCategory04],
[PurchasingCategory05Code],
[PurchasingCategory05],
[PurchasingCategory06Code],
[PurchasingCategory06],
[PurchasingCategory07Code],
[PurchasingCategory07],
[PurchasingCategory08Code],
[PurchasingCategory08],
[PurchasingCategory09Code],
[PurchasingCategory09],
[PurchasingCategory10Code],
[PurchasingCategory10],
[ProductGroupCode],
[ProductGroup],
[ItemPriceGroupCode],
[ItemPriceGroup],
[StockingTypeCode],
[StockingType],
[InsertIndicator],
[InsertDate],
[ChangeIndicator],
[ChangeDate],
[DeleteIndicator],
[DeleteDate],
[PurgeDate]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO






/****** Object:  Index [PK_Star_Item]    Script Date: 07/27/2012 13:08:01 ******/
ALTER TABLE [dbo].[Star_Item] ADD  CONSTRAINT [PK_Star_Item] PRIMARY KEY CLUSTERED 
(
	[ItemNumber] ASC,
	[SourceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


/****** Object:  Index [missing_index_172]    Script Date: 07/27/2012 13:08:01 ******/
CREATE NONCLUSTERED INDEX [missing_index_172] ON [dbo].[Star_Item] 
(
	[SalesCategory03Code] ASC
)
INCLUDE ( [ItemNumberID],
[SourceID],
[ItemNumber],
[ItemNumber02]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

/****** Object:  Index [missing_index_433]    Script Date: 07/27/2012 13:08:01 ******/
CREATE NONCLUSTERED INDEX [missing_index_433] ON [dbo].[Star_Item] 
(
	[Description01] ASC
)
INCLUDE ( [ItemNumberID],
[SourceID],
[ItemNumber02]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

